//
//  DataBaseHandle.m
//  UI18_DataBase
//
//  Created by dllo on 15/11/18.
//  Copyright (c) 2015年 YXY. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
@interface DataBaseHandle ()
@property (nonatomic, copy)NSString *dataBasePath;
@property (nonatomic,retain) NSMutableArray *muArray;
@end
//定义全局变量 - 只保证存在一个DataBaseHandle
static DataBaseHandle *dataBaseHandle = nil;
@implementation DataBaseHandle

//获取数据库保存的路径
-(NSString *)dataBasePath {
    if (!_dataBasePath) {
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *path = [paths lastObject];
        self.dataBasePath = [path stringByAppendingString:@"/topit.sqlite"];
    }
    return _dataBasePath;
}

+ (instancetype)shareDataBase {
    //以这种方法保证整个工程无论什么时候调用，都会返回相同的dataBaseHandle
    if (!dataBaseHandle) {
        dataBaseHandle = [[DataBaseHandle alloc] init];
    }
    return dataBaseHandle;
}

//数据库指针
static sqlite3 *db = nil;
//打开数据库

- (void)openDataBase {

    
    //如果数据库存在就打开已经存在的数据库， 如果数据库不存在则创建再打开
    int result = sqlite3_open(self.dataBasePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        
    }else {
      
    }
}
//关闭数据库
- (void)closedDataBase {
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {

    }else {

    }
}


//创建表
- (void)createTable {
    //1.准备SQL语句
    NSString *sqlString = @"create table if not exists topit (id integer primary key autoincrement not null,title text not null, url text not null)";
    //2.执行SQL语句
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
   
    }else {
 
    }
}

//插入数据

- (void)insertDatabase:(NSString *)title withUrl:(NSString *)url {
    //1.准备SQL语句
    NSString *sqlString = @"insert into topit(title, url) values (?,?)";
    //2.获取一个伴随指针（绑定参数， 获取数据）- 数据库的状态指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.绑定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, url.UTF8String, -1, NULL);
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
     
        }else {
        
        }
    }else {
     
    }
    //6.关闭伴随指针
    sqlite3_finalize(stmt);

}
//查询
- (NSMutableArray *)selectDataBase {
    [self.muArray removeAllObjects];
    //1.准备SQL语句
    NSString *sqlString = @"select title,url from topit";
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //绑定参数
//        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        //执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //从伴随指针获取数据(1列)
            NSString *selectTitle = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 0)];
            NSString *selectUrl = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 1)];
            self.title = [NSDictionary dictionaryWithObjectsAndKeys:selectUrl, @"URL", selectTitle, @"title", nil];
            
            [self.muArray addObject:self.title];
        }
        
    }else {
       
    }
    //6.关闭伴随指针
    sqlite3_finalize(stmt);
    return self.muArray;
}
//    cell.detailTextLabel.text = [_dataSource[indexPath.row] objectForKey:@"number"];

-(NSMutableArray *)muArray{
    if (!_muArray) {
        self.muArray = [[NSMutableArray alloc]init];
    }
    return _muArray;
}

//修改
- (void)updateDataBase:(NSString *)title {
    //1.sql
    NSString *sqlString = @"update topit set age = 20 where age = ?";
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.确定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            
        }else {
          
        }
    }else {
       
    }
    //6.关闭指针
    sqlite3_finalize(stmt);
}
//删除
- (void)deleteDataBase:(NSString *)title {
    //1.sql
    NSString *sqlString = @"delete from topit where title = ?";
    //2.伴随指针
    sqlite3_stmt *stmt = NULL;
    //3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //4.确定参数
        sqlite3_bind_text(stmt, 1, title.UTF8String, -1, NULL);
        //5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
          
        }else {
           
        }
    }else {
    
    }
    //6.关闭指针
    sqlite3_finalize(stmt);
    
}

@end
